package postpic.server.photo;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import postpic.domain.Comment;
import postpic.domain.Photo;
import postpic.server.db.Query;
import postpic.server.user.UserDao;

public class PhotoDao {
	
	private String sql = "";
	private UserDao userDao;
	
	public PhotoDao() {
		
	}
	
	public Photo getPhoto(final Integer photoId) {
		return null;
	}
	
	public List<Comment> getComments(final Integer photoId) {
		List<Comment> comments = new ArrayList<Comment>();
		Comment comment = new Comment();
		
		sql =	"SELECT " +
				"	PC.comment_id, PC.comment_text, PC.comment_date, PC.photo_id, US.first_name, US.last_name " +
				"FROM photo_comments PC " +
				"INNER JOIN user US " +
				"	ON PC.user_id = US.user_id" +
				" WHERE " +
				"	photo_id=" + photoId;
		
		Query q = new Query(sql);
		ResultSet rs = q.execute();
		
		try {
			while(rs.next())
			{
				comment.setCommentId(rs.getInt("comment_id"));
				comment.setUserName(rs.getString("first_name") + " " + rs.getString("last_name"));
				comment.setCommentText(rs.getString("comment_text"));
				comment.setCommentDate(rs.getDate("comment_date"));
				comment.setPhotoId(rs.getInt("photo_id"));
				
				comments.add(comment);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return comments;		
	}
	
	public List<Photo> getRecentPhotos(final Integer userId) {
		List<Photo> photos = new ArrayList<Photo>();
		Photo photo = new Photo();
		
		sql =	"SELECT " +
				"	PH.user_id, PH.photo_id, PH.photo_caption, PH.date_added, PH.photo_url " +
				"FROM photos PH " +
				"INNER JOIN user US " +
				"	ON PH.user_id = US.user_id " +
				"INNER JOIN friend_relation FR " +
				"	ON US.user_id = FR.user_id " +
				"WHERE " +
				"	US.user_id='" + userId + "' " +
				"ORDER BY PH.date_added " +
				"LIMIT 15";
		
		Query q = new Query(sql);
		ResultSet rs = q.execute();
		
		try {
			while(rs.next())
			{
				 photo.setPhotoId(rs.getInt("photo_id"));
				 photo.setComments(getComments(rs.getInt("photo_id")));
				 photo.setCaption(rs.getString("photo_caption"));
				 photo.setPhotoUrl(rs.getString("photo_url"));
				 photo.setDateAdded(rs.getDate("date_added"));
				 photo.setUserId(rs.getInt("user_id"));
				 
				 photos.add(photo);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return photos;
	}
	
	public boolean addPhoto(final Photo photo) {
		sql =	"INSERT INTO photos " +
				"(" +
				"	user_id, photo_url, photo_caption " +
				")" +
				"VALUES " +
				"(" +
					"'" + photo.getUserId() + "','" + photo.getPhotoUrl() + "','" + photo.getCaption() + "'" +
				")";
		
		Query q = new Query(sql);
		return (q.executeUpdate() == 1);
	}

}
